//SIM-UOW CSCI321 FYP
//Group:SS142B
//Tittle: Air Traffic Controller
//Group Leader: Zhou YiFan
//Group Member: Chen Cheng, Song JieYu, Ellen Tan
//This code is a Data Server which provide data operation between Client/Server and mySQL Database

package dataserver;

import java.sql.Connection;
import java.sql.*;
import java.util.Scanner;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataServer {
        
    //Create a Connection to SQL Database    
    public Connection conn = null;
    public Connection getConnection(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/atcsystem";
            String user = "ATCadmin";
            String passWord ="fg9UF6fH7Zm9";
                
            conn = DriverManager.getConnection(url,user,passWord);
             
            if(conn != null){
                System.out.println("SQL Database Connecting Sucessful!");
            }
                
        }catch(Exception e){
            e.printStackTrace();
        }
            
        return conn;
    }
    
    public String getCommand(String operateCommand, String tableCommand, String objectCommand, String objectLocation){
        String returnResult = "";

        //Test for Query
        //operateCommand = "Query";
        //objectCommand = "All";
        //objectCommand = "Speed,Track";
        //tableCommand = "airplane";
        //objectLocation = "Registration='780127'";            
        
        //Test for Insert
        //operateCommand = "Insert";
        //objectCommand = "'780128','Airbus A330-342','Cathay Pacific','F-WMKC2',6166,863,0,158,4.5148,102.9794,'OK'";
        //tableCommand = "airplane";
        
        //Test for Upadte
        //operateCommand = "Update";
        //objectCommand = "Speed='890',Track='180'";
        //objectLocation = "Registration='780128'";
        //tableCommand = "airplane";
        
        //Test for Delete
        //operateCommand = "Delete";
        //objectLocation = "Registration='780128'";
        //tableCommand = "airplane";
        
        switch (operateCommand){
            case "Query":
                System.out.println("Query...");
                returnResult = Query(objectCommand, objectLocation, tableCommand); 
                System.out.println(returnResult);
                break;
            case "Insert":
                System.out.println("Insert...");
                returnResult = Insert(objectCommand, tableCommand);
                System.out.println(returnResult);
                break;
            case "Update":
                System.out.println("Update...");
                returnResult = Update(objectCommand, objectLocation, tableCommand);
                System.out.println(returnResult);
                break;
            case "Delete":
                System.out.println("Delete...");
                returnResult = Delete(objectLocation, tableCommand);
                System.out.println(returnResult);
                break;
            default:
                break;
        }
        
        return returnResult;
    }

    public String Query(String objectCommand, String objectLocation, String tableCommand){
        String returnResult[] = new String[20];
        String returnQueryState = "";        
        String SQLCommand = "";
        String SQLObjectCommand = "";
        String eachQueryItem[]= new String[20];                                
        
        if(objectCommand.equals("All") ){
            SQLObjectCommand = "*";
        }
        else{
            SQLObjectCommand = objectCommand;
            //Process object Command
            eachQueryItem = objectCommand.split(","); 
        }
        
        SQLCommand = "select " + SQLObjectCommand + " from " + tableCommand + " where " + objectLocation;
        
        try{
            Statement state = conn.createStatement();                                   
            System.out.println(SQLCommand);            
            
            if(objectCommand.equals("All") ){
                 //use for count the columns of SQL Database Table
                ResultSet checkColumn = state.executeQuery("select count(*) from information_schema.`COLUMNS` "
                        + "where TABLE_SCHEMA='atcsystem' and TABLE_NAME='" + tableCommand + "'"); 
                int columnCount = 0; 
                while(checkColumn.next()) { 
                columnCount = checkColumn.getInt("count(*)");                                 
                }
                System.out.println("Columns: " + columnCount);
                
                //ues for check column names of Table
                ResultSet checkColumnName = state.executeQuery(SQLCommand);
                ResultSetMetaData metadata=checkColumnName.getMetaData();                 
                while(checkColumnName.next()) {              
                    for (int i = 1; i <= columnCount; i++){                        
                        eachQueryItem[i-1] = metadata.getColumnName(i);
                        //System.out.println(eachQueryItem[i-1]);
                    }        
                } 
                
                //read all data in a row
                ResultSet res = state.executeQuery(SQLCommand);
                while(res.next()) {                
                    for (int i = 0; i < columnCount; i++){
                        returnResult[i] = res.getString(eachQueryItem[i]);
                        
                        if(i != (columnCount-1) ) {
                            returnQueryState = returnQueryState + returnResult[i] + ",";
                        } else {
                            returnQueryState = returnQueryState + returnResult[i];
                        }
                            
                    }        
                }                
            }else{
                //read specific data in a row
                ResultSet res = state.executeQuery(SQLCommand);
                 while(res.next()) {         
                    int tempItemLength = eachQueryItem.length;
                    for (int i = 0; i < tempItemLength; i++){
                        returnResult[i] = res.getString(eachQueryItem[i]);
                        
                        if(i != (tempItemLength-1) ) {
                            returnQueryState = returnQueryState + returnResult[i] + ",";
                        } else {
                            returnQueryState = returnQueryState + returnResult[i];
                        }
                    }        
                }
            }
            
            state.close();
            
        }catch(SQLException e){
            e.printStackTrace();
        }
                
        return returnQueryState;
    }    
    
    public String Insert(String InsertItem, String tableCommand){
        String returnInsertState = "";
        String SQLCommand = "";
        
        SQLCommand = "insert into " + tableCommand + " values(" + InsertItem + ")";        
        
        try{
            Statement state = conn.createStatement();
            
            System.out.println(SQLCommand);
            state.execute(SQLCommand);
            
            returnInsertState = "Insert: " + state.getUpdateCount();
            
            state.close();
            
        }catch(SQLException e){
            e.printStackTrace();
        }
        
        return returnInsertState;
    }
    
    public String Update(String UpdateItem, String objectLocation, String tableCommand){
        String returnUpdateState = "";
        String SQLCommand = "";
        
        SQLCommand = "update " + tableCommand + " set " + UpdateItem + " where " + objectLocation;
        
        try{
            Statement state = conn.createStatement();
            
            System.out.println(SQLCommand);
            state.executeUpdate(SQLCommand);
            
            returnUpdateState = "Update: " + state.getUpdateCount();
            //System.out.println(returnUpdateState);
            
            state.close();
            
        }catch(SQLException e){
            e.printStackTrace();
        }                
        
        return returnUpdateState;
    }
    
    public String Delete(String objectLocation, String tableCommand){
        String returnDeleteState = "";        
        String SQLCommand = "";
        
        SQLCommand = "delete from " + tableCommand + " where " + objectLocation;
        
        try{
            Statement state = conn.createStatement();
            
            System.out.println(SQLCommand);
            state.executeUpdate(SQLCommand);
            
            returnDeleteState = "Delete: " + state.getUpdateCount();
            //System.out.println(returnUpdateState);
            
            state.close();
            
        }catch(SQLException e){
            e.printStackTrace();
        }
                      
        return returnDeleteState;
    }
    
    public static void main(String[] args) {
        //Use for Test
        String operateCommand = "";
        String objectCommand = "";
        String objectNumCommand = "";
        String tableCommand = "";
        String objectLocation = "";        
        
        // TODO code application logic here
        Scanner cin = new Scanner (System.in);
        
        System.out.println("Air Traffic Controler System Data Server");
        System.out.println("=========================================");
        
        DataServer ds = new DataServer();
        ds.getConnection();
        
        System.out.println("Waiting for Connection...");
        System.out.println("or... enter Command directly...\n");   
        
        //getConn.getCommand(operateCommand, objectCommand, tableCommand, objectLocation);
        //getConn.getCommand("Query","flight","Flight,Departure","Registration='8A01A7'");
        String tempResults //= ds.getCommand("Query","runwaysstatus","All","id='1'") + ";";
                //tempResults += ds.getCommand("Query","runwaysstatus","All","id='2'");
                = ds.getCommand("Update","user","Status='online'","Username='chencheng'");
                System.out.println(tempResults);
    }
    
}
